EXPLAIN QUERY PLAN 

=================================================================

⚙️ 3. Trigger: after insert on main_Log
CREATE TRIGGER trg_main_Log_insert
AFTER INSERT ON main_Log
BEGIN
    UPDATE recordCounter
    SET tableCount = tableCount + 1
    WHERE tableName = 'main_Log';
END;


⚙️ 4. Trigger: after delete on main_Log
CREATE TRIGGER trg_main_Log_delete
AFTER DELETE ON main_Log
BEGIN
    UPDATE recordCounter
    SET tableCount = tableCount - 1
    WHERE tableName = 'main_Log';
END;


To get current count instantly:
SELECT tableCount FROM recordCounter WHERE tableName = 'main_Log';



PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM main_log;

DROP TABLE main_log;

CREATE TABLE main_log (
    pk_key    INTEGER  PRIMARY KEY
                       NOT NULL,
    timeStamp DATETIME NOT NULL
                       DEFAULT (CURRENT_TIMESTAMP),
    logType   TEXT,
    logCode   TEXT,
    logPgm    TEXT,
    dataLine  TEXT,
    clientID  TEXT
);

INSERT INTO main_log (
                         pk_key,
                         timeStamp,
                         logType,
                         logCode,
                         logPgm,
                         dataLine,
                         clientID
                     )
                     SELECT pk_key,
                            timeStamp,
                            logType,
                            logCode,
                            logPgm,
                            dataLine,
                            clientID
                       FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

PRAGMA foreign_keys = 1;


=================================================================

CREATE INDEX mainLog_index01 ON main_log (
    timeStamp ASC,
    pk_key ASC
);

CREATE INDEX mainLog_index02 ON main_log (
    timeStamp ASC,
    logType ASC,
    pk_key ASC
);

CREATE INDEX mainLog_index03 ON main_log (
    timeStamp ASC,
    logCode ASC,
    pk_key ASC
);

CREATE INDEX mainLog_index04 ON main_log (
    timeStamp ASC,
    logPgm ASC,
    pk_key ASC
);


CREATE INDEX mainLog_index05 ON main_log (
    timeStamp ASC,
    dataline,
    pk_key ASC
);


=================================================================


CREATE INDEX mainLog_index02 ON main_log (
    timeStamp DESC,
    pk_key ASC
);




CREATE INDEX mainLog_index04 ON main_log (
    logType DESC,
    pk_key ASC
);



CREATE UNIQUE INDEX mainLog_index06 ON main_log (
    logCode DESC,
    pk_key ASC
);


CREATE UNIQUE INDEX mainLog_index08 ON main_log (
    logPgm DESC,
    pk_key ASC
);


CREATE INDEX mainLog_index09 ON main_log (
    timeStamp ASC,
    logType ASC, 
    pk_key ASC
);




CREATE TRIGGER trg_reeft_customer_insert
         AFTER INSERT
            ON reeft_customer
BEGIN
    UPDATE recordCounter
       SET tableCount = tableCount + 1
     WHERE tableName = 'reeft_customer';
END;


INSERT INTO recordCounter VALUES ( 1, 'reeft_customer',  (SELECT COUNT(*) FROM reeft_customer));